#!/usr/bin/python

import ystockquote
from threading import Thread
import Queue
import time
import json
import simplejson
import urllib2 as ul
import talib

execfile("/opt/python/includes/mysql.py")
work=Queue.Queue()
doWork=True

def datefromsecs(date):
    return time.strftime("%d-%m-%Y %H:%M",time.gmtime(date))

def jsonFromURL(url):
    i=0
    prices=None
    while (prices==None or prices['query']['results'] == None) and i<10:
        #print(url)
        prices=simplejson.load(ul.build_opener().open(url))
        i=i+1
    return prices

def insertOHLC(symb, open, high, low, close, volume, date,db,dfmt="%Y-%m-%d"):
    if volume == 0: return
    t=time.strptime(date,dfmt)
    secs=time.mktime((t.tm_year, t.tm_mon, t.tm_mday, 0, 0, 0, t.tm_wday, t.tm_yday, 0))
    symb=ul.unquote(symb)
    #print "Entering prices for "+date
    db.query("INSERT INTO prices ( open, high, low, close, volume, date, symbol ) VALUES ( "+
                                  open+","+high+","+low+","+ close+","+volume+","+str(secs)+",'"+symb+"' ) "+
                                  "ON DUPLICATE KEY UPDATE open="+open+", "+ "high="+high+", "+
                                                        "low ="+low+", "+ "close="+close+", "+ "volume="+volume)

def getIndexComponents(index):
    page=0
    indexcomps=jsonFromURL('http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%27http%3A%2F%2Fuk.finance.yahoo.com%2Fq%2Fcp%3Fs%3D'+ul.quote(ul.quote(index))+'%26c%3D'+str(page)+'%27%20%20and%20xpath%3D%22%2F%2Ftable%2Ftr%2Ftd%2Fstrong%2Fa%22&format=json&callback=')
    indices=[]
    while(indexcomps['query']['results']  != None):
        il=indexcomps['query']['results']['a']
        if type(il) is dict: il = [ il ]
        for ic in il:
            indices.append(ic['content'])
        page=page+1
        indexcomps=jsonFromURL('http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20html%20where%20url%3D%27http%3A%2F%2Fuk.finance.yahoo.com%2Fq%2Fcp%3Fs%3D'+ul.quote(ul.quote(index))+'%26c%3D'+str(page)+'%27%20%20and%20xpath%3D%22%2F%2Ftable%2Ftr%2Ftd%2Fstrong%2Fa%22&format=json&callback=')
    return indices

def storeThread():
    global doWork
    while doWork:
        #print "Reading Work..."
        try:
            db=getDBCon('fi')
            details=work.get(timeout=2)
            prices=jsonFromURL('http://query.yahooapis.com/v1/public/yql?'+
                               'q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20%3D%20%22'+ul.quote(details['sy'])+'%22%20'+
                               'and%20startDate%20%3D%20%22'+details['then']+'%22%20'+
                               'and%20endDate%20%3D%20%22'+details['now']+'%22&'+
                               'format=json&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=')
            for entry in prices['query']['results']['quote']:
                insertOHLC(entry['Symbol'], entry['Open'], entry['High'], entry['Low'], entry['Adj_Close'], entry['Volume'], entry['Date'],db)
            prices=jsonFromURL('http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quoteslist%20where%20symbol%3D%27'+ul.quote(details['sy'])+'%27&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=')
            i=0
            while(prices['query']['results']['quote']['DaysHigh'] == None and i<10):
                prices=jsonFromURL('http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quoteslist%20where%20symbol%3D%27'+ul.quote(details['sy'])+'%27&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=')
            if(prices['query']['results']['quote']['DaysHigh'] != None):
                entry=prices['query']['results']['quote']
                #print entry
                insertOHLC(entry['Symbol'], entry['Open'], entry['DaysHigh'], entry['DaysLow'], entry['LastTradePriceOnly'], entry['Volume'], entry['LastTradeDate'],db,"%m/%d/%Y")
            else: print "Error inserting for "+details['sy']
            db.close()
        except Exception as inst:
            #print "Done: "+str(type(inst))
            #print inst
            break

def getAndStore(symb=[], offset=0,threads=15):
    global doWork
    t=time.localtime()
    now=time.strftime("%Y-%m-%d", time.localtime(time.mktime((t.tm_year-offset,t.tm_mon,t.tm_mday,0,0,0,t.tm_wday,t.tm_yday,0))))
    then=time.strftime("%Y-%m-%d", time.localtime(time.mktime((t.tm_year-offset-1,t.tm_mon,t.tm_mday, 0, 0, 0, t.tm_wday, t.tm_yday, 0))))
    for sy in symb: 
        work.put({"sy": sy, "now": now, "then": then})
    # End For sy
    threadList=[]
    for i in range(threads):
        threadList.append(Thread(target=storeThread))
        threadList[-1].start()
    # End For i
    while not work.empty():
        time.sleep(1)
        #print "Work waiting"
    doWork=False
    for t in threadList:
        #print "Thread wait"
        t.join()
# End def

def getOHLCDict(symb, format='ohlcGroups'):
    db=getDBCon('fi')
    r=getResult('SELECT open, high, low, close, date, volume from prices where symbol = "'+symb+'" and date>'+str(time.time()-5*365*24*60*60)+" order by date",db);
    row=r.fetch_row()
    #print("Format: "+format)
    if(format=='ohlcGroups'):
        j_sn={ 'open': [], 'high': [], 'low': [], 'close': [], 'volume': [], 'date': [] }
        #print("Writing OHLC sequence")
        while(len(row)>0):
            j_sn['open'].append(float(row[0][0]))
            j_sn['high'].append(float(row[0][1]))
            j_sn['low'].append(float(row[0][2]))
            j_sn['close'].append(float(row[0][3]))
            j_sn['date'].append(float(row[0][4]))
            j_sn['volume'].append(float(row[0][5]))
            row=r.fetch_row()
    elif(format=='ohlcObjects'):
        j_sn=[]
        #print("Writing OHLC objects")
        while(len(row)>0):
            j_sn.append({'open': float(row[0][0]), 'high': float(row[0][1]), 'low': float(row[0][2]), 'close': float(row[0][3]), 'date': float(row[0][4]), 'volume': float(row[0][5])})
            row=r.fetch_row()
    db.close()
    return j_sn

def getPriceValues(symb):
    return json.dumps(getOHLCDict(symb,'ohlcObjects'))

def getPriceGraphData(symb):
    priceDict=getOHLCDict(symb,'ohlcObjects')
    graph=[]
    for object in priceDict:
        graph.append({ 'x': object['date'], 'y': { 'open': object['open'], 'high': object['high'], 'low': object['low'], 'close': object['close'] } });
    return json.dumps(graph);

def getIndicatorValues(symb, ind, index):
    ohlc=getOHLCDict(symb)
